#!/bin/bash

source /etc/profile

if [ -n "$1" ] ;then
	do_date=$1
else
	do_date=`date -d "-1 day" +%F`
fi

sql="
with tmp as(
select device_id, dt, date_sub(dt ,row_number() over (partition by device_id order by dt)) as gid from 
dws.dws_member_start_day 
where 
dt >= date_add('$do_date', -6)
and dt <= '$do_date'
)
insert overwrite table ads.ads_three_days_active_member_cnt
partition(dt='$do_date')
select count(*) as cnt from (
select device_id, count(*) logincount from tmp group by device_id, gid having logincount>=3) t1;
"

hive --hiveconf hive.exec.mode.local.auto.input.files.max=20 -e "$sql"



